package com.hzdl.car.util;

import com.hzdl.car.bean.Customer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {


        /**
         * 使用客户信息数据，显示到Excel中,返回ByteArrayOutputStream
         */
        public static ByteArrayOutputStream  createExcelByCustomer(List<Customer> list , String sheetName ){
            // 创建excel对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建sheet
            HSSFSheet sheet =  workbook.createSheet(sheetName);

            // 设置单元格默认宽高
            sheet.setDefaultRowHeight((short)(20*20));
            sheet.setDefaultColumnWidth(20);
            // 合并第一行单元格
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0 , 0 , 0 ,6);
            sheet.addMergedRegion(cellRangeAddress);
            // 合并第二行单元格
            CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1 , 1 , 0 ,6);
            sheet.addMergedRegion(cellRangeAddress2);

            // 设置值
            // 第一行
            int rownum = 0;
            HSSFRow row1 =  sheet.createRow(rownum);
            HSSFCell row1_cell1 = row1.createCell(0);
            row1_cell1.setCellValue(sheetName);
            // 设置样式
            HSSFCellStyle cellStyle1 = workbook.createCellStyle(); // 从workbook里面获取样式对象
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 左右居中
            cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

            HSSFFont font1 = workbook.createFont();
            font1.setBold(true);// 是否加粗
            font1.setFontName("宋体"); // 设置字体
            font1.setFontHeight((short)(30*10));
            cellStyle1.setFont(font1);

            row1_cell1.setCellStyle(cellStyle1); // 把样式设置到单元格



            // 第二行
            rownum++;
            HSSFRow row2 = sheet.createRow(rownum);
            HSSFCell row2_cell1 = row2.createCell(0);
            row2_cell1.setCellValue("总条数："+list.size()+" 导出时间:" + new Date().toLocaleString());
            // 设置样式
            HSSFCellStyle cellStyle2 = workbook.createCellStyle(); // 从workbook里面获取样式对象
            cellStyle2.setAlignment(HorizontalAlignment.CENTER); // 左右居中
            cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

            HSSFFont font2 = workbook.createFont();
//            font2.setColor(HSSFColor.GREEN.index); // 设置字体颜色
            font2.setFontName("宋体"); // 设置字体
            font2.setFontHeight((short)(20*20));
            cellStyle2.setFont(font2);

            row2_cell1.setCellStyle(cellStyle2);


            // 第三行
            rownum++;
            HSSFRow row3 = sheet.createRow(rownum);
            String[] titleArr= {"身份证号","客户姓名","客户性别","地址","联系电话","职位","创建时间"};
            for (int i = 0; i < titleArr.length ; i++){
                HSSFCell row3_cellx = row3.createCell(i);
                row3_cellx.setCellValue(titleArr[i]);

                // 设置样式
                HSSFCellStyle cellStyle3 = workbook.createCellStyle(); // 从workbook里面获取样式对象
                cellStyle3.setAlignment(HorizontalAlignment.CENTER); // 左右居中
                cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

                HSSFFont font3 = workbook.createFont();
//                font3.setColor(HSSFColor.RED.index); // 设置字体颜色
                font3.setFontName("行书"); // 设置字体
                font3.setFontHeight((short)(18*20));
                cellStyle3.setFont(font3);

                row3_cellx.setCellStyle(cellStyle3);
            }



            // 第四行 到结尾
            for(Customer customer : list){

                rownum++;
                HSSFRow rowx = sheet.createRow(rownum);

                // 设置样式
                HSSFCellStyle cellStylex = workbook.createCellStyle(); // 从workbook里面获取样式对象
                cellStylex.setAlignment(HorizontalAlignment.CENTER); // 左右居中
                cellStylex.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

                HSSFCell rowx_cell1 =  rowx.createCell(0);
                rowx_cell1.setCellValue(customer.getIdentity());
                rowx_cell1.setCellStyle(cellStylex);

                HSSFCell rowx_cell2 =  rowx.createCell(1);
                rowx_cell2.setCellValue(customer.getCustName());
                rowx_cell2.setCellStyle(cellStylex);

                HSSFCell rowx_cell3 =  rowx.createCell(2);
                rowx_cell3.setCellValue(customer.getSex()== 0 ?"女":"男");
                rowx_cell3.setCellStyle(cellStylex);

                HSSFCell rowx_cell4 =  rowx.createCell(3);
                rowx_cell4.setCellValue(customer.getAddress());
                rowx_cell4.setCellStyle(cellStylex);

                HSSFCell rowx_cell5 =  rowx.createCell(4);
                rowx_cell5.setCellValue(customer.getPhone());
                rowx_cell5.setCellStyle(cellStylex);

                HSSFCell rowx_cell6 =  rowx.createCell(5);
                rowx_cell6.setCellValue(customer.getCareer());
                rowx_cell6.setCellStyle(cellStylex);

                HSSFCell rowx_cell7 =  rowx.createCell(6);
//                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");

                rowx_cell7.setCellValue(customer.getCreateTime());

                rowx_cell7.setCellStyle(cellStylex);

            }

            try {
                ByteArrayOutputStream fos = new ByteArrayOutputStream();
                workbook.write(fos);

                return fos;
            } catch (Exception e) {
                e.printStackTrace();
            }

            return  null;



        }

}
